import pymysql
import csv


# 数据库操作工具类
class DbHelper:

    def __init__(self,db): #设置操作的库属性
        self.db = db

    # 获取游标对象
    def getconn(self):
        conn = pymysql.connections.Connection(host="127.0.0.1",port=3306,user="root",passwd="root",db=self.db,charset="utf8")
        return conn

    # sql要执行增删改的sql语句 data代表的是sql中需要进行替换的参数元组
    def update(self,sql,data):
        try:
            #获取游标对象
            conn=self.getconn()
            cursor=conn.cursor()
            #执行sql
            n=cursor.execute(sql,data)
            #提交事务
            conn.commit()
            return n
        except Exception as e:
            print(e,"连接异常")
        finally:
            #释放资源
            cursor.close()
            conn.close()

    def queryOne(self,sql,data):
        try:
            #获取游标对象
            conn=self.getconn()
            cursor=conn.cursor()
            #执行sql
            n=cursor.execute(sql,data)
            #获取数据
            dataResult=cursor.fetchone()
        except Exception as e :
            print(e)
        finally:
            #释放资源
            cursor.close()
            conn.close()
        return dataResult

    def queryAll(self, sql, data):
        try:
            # 获取游标对象
            conn = self.getconn()
            cursor = conn.cursor()
            # 执行sql
            n = cursor.execute(sql, data)
            # 获取数据
            dataResult = cursor.fetchall()
        except Exception as e:
            print(e)
        finally:
            # 释放资源
            cursor.close()
            conn.close()
        return dataResult




def getCsvData(path):
    #获取csv文件对象
    file = open(path,'r',encoding='utf-8')
    #使用csv模块读取该文件
    reader = csv.reader(file)
    dataList = []
    #遍历reader
    for row in reader:
        dataList.append(row)
    file.close()
    return dataList



